﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

/// <summary>
/// Summary description for NotesDB
/// </summary>
namespace Notes
{
    public class NotesDB
    {

        DbCommand cm = null;
        DataSet ds = null;
        SqlDatabase db = null;
        IDataReader dr = null;
        string conStr =System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringDev"].ToString();

        public string Title { get; set; }

        public string Description { get; set; }
        public string FileName { get; set; }

        public Int16 StreamID { get; set; }

        public Int16 SubjectID { get; set; }

        public Int16 UniversityID { get; set; }

        public HttpPostedFile Document { get; set; }

        public int FileID { get; set; }

        public int UserID { get; set; }

        public Int32 DocumentID { get; set; }

        public Int16 DocTypeID { get; set; }

        public Int16 DocExtID { get; set; }
        
        public IDataReader Select_Streams()
        {
            return SelectDataReader_ProcName("NTS.Select_Streams");
        }
    
        public IDataReader Select_Universities()
        {
            return SelectDataReader_ProcName("NTS.Select_Universities");
        }

        public IDataReader SelectDataReader_ProcName(string ProcName)
        {
            db = new SqlDatabase(conStr);
            using (cm = db.GetStoredProcCommand(ProcName))
            {
                return db.ExecuteReader(cm);
            }

        }

        public IDataReader Select_Subjects(int StreamID)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Nts.Select_Subjects"))
                {
                    db.AddInParameter(cm, "@StreamID", DbType.Int32, StreamID);
                    return db.ExecuteReader(cm);

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public IDataReader Select_College(int UnivID)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Nts.Select_College"))
                {
                    db.AddInParameter(cm, "@UnivID", DbType.Int32, UnivID);
                    return db.ExecuteReader(cm);

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public int Insert_Document()
        {
            FileName = string.Empty;
            int nFileLen = 0;
            byte[] FileBuffer = null;
            string FileExt = string.Empty;
            if (this.Document != null)
            {
                if (this.Document.ContentLength > 0)
                {
                    FileName = System.IO.Path.GetFileName(this.Document.FileName);
                    nFileLen = this.Document.ContentLength;
                    FileExt = this.Document.ContentType;
                    System.IO.Stream objStream = this.Document.InputStream;
                    FileBuffer = new byte[nFileLen];
                    objStream.Read(FileBuffer, 0, nFileLen);
                }
            }
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Nts.Insert_Document"))
                {
                    db.AddInParameter(cm, "@UserID", DbType.Int16, this.UserID);
                    db.AddInParameter(cm, "@Title", DbType.String, this.Title);
                    db.AddInParameter(cm, "@Desc", DbType.String, this.Description);
                    db.AddInParameter(cm, "@StreamID", DbType.Int16, this.StreamID);
                    db.AddInParameter(cm, "@SubjectID", DbType.Int16, this.SubjectID);
                    db.AddInParameter(cm, "@UniversityID", DbType.Int16,this.UniversityID);
                    db.AddInParameter(cm, "@DocTypeID", DbType.Int16, this.DocTypeID);
                    db.AddInParameter(cm, "@DocExt", DbType.String, FileExt);
                    db.AddInParameter(cm, "@DocLength", DbType.Int64, nFileLen);
                    db.AddInParameter(cm, "@DocData", DbType.Binary, FileBuffer);
                    db.AddInParameter(cm, "@FileName", DbType.String, FileName);
                    dr = db.ExecuteReader(cm);
                    if (!object.Equals(dr, null))
                    {
                        while (dr.Read())
                        {
                            return Convert.ToInt32(dr["FileID"]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //ErrHandler.WriteError(ex.Message);
                throw new Exception(ex.Message);
            }
            return 0;
        }

        public IDataReader Select_TopDocuments()
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Nts.Select_TopDocuments"))
                { 
                    return db.ExecuteReader(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public DataSet Select_SearchDocuments()
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Nts.Select_SearchDocuments"))
                {

                    db.AddInParameter(cm, "@Title", DbType.String, this.Title);
                    db.AddInParameter(cm, "@StreamID", DbType.Int16,this.StreamID);
                    db.AddInParameter(cm, "@SubjectID", DbType.Int16, this.SubjectID);
                    db.AddInParameter(cm, "@DocTypeID", DbType.Int16, this.DocTypeID);
                   // db.AddInParameter(cm, "@UnivID", DbType.Int16, UnivID);
                   // db.AddInParameter(cm, "@IncreaseDnd", DbType.Boolean, IncreaseDnd);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet Select_DocsMaster(int DocID, string ViewType = "", int UserID = 0, int StreamID = 0, int SubjectID = 0, int UnivID = 0, bool IncreaseDnd = false, Int16? DocType=0)
        {
            ds = new DataSet();
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Nts.Select_DocsMaster"))
                {
                    db.AddInParameter(cm, "@DocID", DbType.Int32, DocID); 
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.AddInParameter(cm, "@ViewType", DbType.String, ViewType);
                    db.AddInParameter(cm, "@StreamID", DbType.Int16, StreamID);
                    db.AddInParameter(cm, "@SubjectID", DbType.Int16, SubjectID);
                    db.AddInParameter(cm, "@UnivID", DbType.Int16, UnivID);
                    db.AddInParameter(cm, "@IncreaseDnd", DbType.Boolean, IncreaseDnd);
                    db.AddInParameter(cm, "@DocType", DbType.Int16, DocType);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public void Activate_Deactivate_Docs(int DocID, int UserID, bool Activate)
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("NTS.Activate_Deactivate_Docs"))
                {
                    db.AddInParameter(cm, "@DocID", DbType.Int32, DocID);
                    db.AddInParameter(cm, "@UserID", DbType.Int32, UserID);
                    db.AddInParameter(cm, "@Activate", DbType.Int32, Activate);
                    db.ExecuteNonQuery(cm);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public int Update_Document()
        {
            int result = 0;
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand("Nts.Update_Document"))
                {
                    db.AddInParameter(cm, "@FileID", DbType.Int16, this.FileID);
                    db.AddInParameter(cm, "@UserID", DbType.Int16, this.UserID);
                    db.AddInParameter(cm, "@Title", DbType.String, this.Title);
                    db.AddInParameter(cm, "@Desc", DbType.String, this.Description);
                    db.AddInParameter(cm, "@StreamID", DbType.Int16, this.StreamID);
                    db.AddInParameter(cm, "@SubjectID", DbType.Int16, this.SubjectID);
                    db.AddInParameter(cm, "@UniversityID", DbType.Int16, this.UniversityID);
                    db.AddInParameter(cm, "@DocTypeID", DbType.Int16, this.DocTypeID); 
                    result = db.ExecuteNonQuery(cm);
                   
                }
            }
            catch (Exception ex)
            {
                //ErrHandler.WriteError(ex.Message);
                throw new Exception(ex.Message);
            }
            return result;
        }
    }
}